package db;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Puhui on 2017/3/29.
 */
public class DbInfo {

    public static void main(String[] args) throws Exception {
        List<String> tables = getTables();
        tables.forEach(e->{
            System.out.println(e);
            try {
                List<String> keys = getPrimaryKeys(e);
                keys.forEach(c->System.out.println("\tkeys:" + c));

                List<String> pkeys = getImportedKeys(e);
                pkeys.forEach(c->System.out.println("\tpkeys:" + c));

                List<String> indexs = getIndexInfo(null, e);
                indexs.forEach(c->System.out.println("\tindex:" + c));

                List<String> columns = getTableColumns(null, e);
                columns.forEach(c->System.out.println("\tcolumns:" + c));
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        });

        System.out.println("size: " + tables.size());

        String ss = "asda|adsa|adfas";
        String[] sss = ss.split("\\|");
        System.out.println("size1: " + sss.length);
    }

    public static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/school?serverTimezone=UTC","root","123456");
    }

    public static void close(Connection connection) throws SQLException {
        if (connection != null) {
            connection.close();
        }
    }

    public static List<String> getTables() throws Exception {
        List<String> result = new ArrayList<String>();
        Connection conn = getConnection();

        try {
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rsTables = meta.getTables("school", null , "%", new String[]{"TABLE"});
            while(rsTables.next()) {
                String tableName = rsTables.getString("TABLE_NAME");
                result.add(tableName);
            }
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return result;
    }

    public static List<String> getTableColumns(String schemaName, String tableName) throws Exception {
        List<String> result = new ArrayList<String>();
        Connection conn = getConnection();
        try{
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rs = meta.getColumns(null, schemaName, tableName, "%");
            while (rs.next()){
                String tableCat = rs.getString("TABLE_CAT");//表目录（可能为空）
                String tableSchemaName = rs.getString("TABLE_SCHEM");//表的架构（可能为空）
                String tableName_ = rs.getString("TABLE_NAME");//表名
                String columnName = rs.getString("COLUMN_NAME");//列名
                int dataType = rs.getInt("DATA_TYPE"); //对应的java.sql.Types类型
                String dataTypeName = rs.getString("TYPE_NAME");//java.sql.Types类型   名称
                int columnSize = rs.getInt("COLUMN_SIZE");//列大小
                int decimalDigits = rs.getInt("DECIMAL_DIGITS");//小数位数
                int numPrecRadix = rs.getInt("NUM_PREC_RADIX");//基数（通常是10或2）
                int nullAble = rs.getInt("NULLABLE");//是否允许为null
                String remarks = rs.getString("REMARKS");//列描述
                String columnDef = rs.getString("COLUMN_DEF");//默认值
                int sqlDataType = rs.getInt("SQL_DATA_TYPE");//sql数据类型
                int sqlDatetimeSub = rs.getInt("SQL_DATETIME_SUB");   //SQL日期时间分?
                int charOctetLength = rs.getInt("CHAR_OCTET_LENGTH");   //char类型的列中的最大字节数
                int ordinalPosition = rs.getInt("ORDINAL_POSITION");  //表中列的索引（从1开始）

                /**
                 * ISO规则用来确定某一列的为空性。
                 * 是---如果该参数可以包括空值;
                 * 无---如果参数不能包含空值
                 * 空字符串---如果参数为空性是未知的
                 */
                String isNullAble = rs.getString("IS_NULLABLE");

                /**
                 * 指示此列是否是自动递增
                 * 是---如果该列是自动递增
                 * 无---如果不是自动递增列
                 * 空字串---如果不能确定它是否
                 * 列是自动递增的参数是未知
                 */
                String isAutoincrement = rs.getString("IS_AUTOINCREMENT");

                result.add(columnName);
            }
        } catch (SQLException e){
            e.printStackTrace();
        }
        return result;
    }

    public static List<String> getPrimaryKeys(String tableName) throws Exception {
        List<String> result = new ArrayList<String>();
        Connection conn = getConnection();

        try {
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rs = meta.getPrimaryKeys("school", null, tableName);
            while(rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");//列名
                short keySeq = rs.getShort("KEY_SEQ");//序列号(主键内值1表示第一列的主键，值2代表主键内的第二列)
                String pkName = rs.getString("PK_NAME"); //主键名称
                result.add(columnName);
            }
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return result;
    }

    public static List<String> getIndexInfo(String schemaName, String tableName) throws Exception {
        List<String> result = new ArrayList<String>();
        Connection conn = getConnection();
        try{
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rs = meta.getIndexInfo(null, schemaName, tableName, true, false);
            while (rs.next()){
                boolean nonUnique = rs.getBoolean("NON_UNIQUE");//非唯一索引(Can index values be non-unique. false when TYPE is  tableIndexStatistic   )
                String indexQualifier = rs.getString("INDEX_QUALIFIER");//索引目录（可能为空）
                String indexName = rs.getString("INDEX_NAME");//索引的名称
                short type = rs.getShort("TYPE");//索引类型
                short ordinalPosition = rs.getShort("ORDINAL_POSITION");//在索引列顺序号
                String columnName = rs.getString("COLUMN_NAME");//列名
                String ascOrDesc = rs.getString("ASC_OR_DESC");//列排序顺序:升序还是降序
                int cardinality = rs.getInt("CARDINALITY");   //基数
                result.add(columnName);
            }
        } catch (SQLException e){
            e.printStackTrace();
        }
        return result;
    }

    public static List<String> getImportedKeys(String tableName) throws Exception {
        List<String> result = new ArrayList<String>();
        Connection conn = getConnection();
        try{
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet pkRSet = meta.getImportedKeys(null, null, tableName);
            while (pkRSet.next()){
                String tName = pkRSet.getString(3);
                String keyName = pkRSet.getString(4);
                String columnName = pkRSet.getString(8);

                result.add(tName + "-" + keyName + "-" + columnName);
            }
        } catch (SQLException e){
            e.printStackTrace();
        }
        return result;
    }
}
